



ALTER      proc sp_wip_inventory_test_report
@process_step_id int,
@process_step_type varchar(20),
@date_from varchar (20),
@date_to varchar(20)

as 
if (@process_step_id <> 0)
begin
set @process_step_id = 1021
declare Process_FlowCursor cursor for 
select ProcessStep_ID from fn_get_process_flow_by_id(@Process_Step_Id)

--declare PS_Type varchar(20)
--declare Process_TypeName varchar (20)



open Process_FlowCursor
declare @Process_StepId bigint
fetch next from Process_FlowCursor into @Process_StepId
while @@fetch_status = 0
begin
print @Process_StepId 
declare Lot_Cursor cursor for 
select Lot_table.Lot_ID from Lot_table, product, Process_Step where Lot_Table.ProcessStep_ID = @Process_StepID and (Lot_table.ProcessStep_ID = product.ProcessStep_ID)  and (Lot_table.Lot_Current_Status = 'FINISHED GOODS' or Lot_table.Lot_Current_Status = 'running' or Lot_table.Lot_Current_Status = 'HOLD' or Lot_table.Lot_Current_Status = 'queue') and Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID and (Process_step.PS_Type_Name = 'Finished Goods' or Process_step.PS_Type_Name = 'Die Bank' or Process_step.PS_Type_Name = 'Class Store')
declare @Lot_ID bigint
declare @Lot_Number varchar(20)
--declare @Location_ID int
declare @Lot_Cost float
declare @Lot_Current_Status varchar (20)
declare @Part_Number varchar (50)
declare @PS_Type_Name varchar (50)

print 'table done' 
declare @results Table(Lot_Number varchar (20), Lot_Cost float, Product varchar (20), Lot_Current_Status varchar (50), Date_In datetime, Date_Out datetime, Qty_In int, Qty_Out int, PS_Type_Name varchar (50)) 


open Lot_Cursor
fetch next from Lot_Cursor into @Lot_ID
print @Lot_ID
while @@fetch_status = 0

begin
declare @Date_In varchar(50)
declare @Date_Out varchar(50)
declare @Qty_In int
declare @Qty_Out int

set @Date_In = (select Inventory_Lot.IL_date_In from Inventory_Lot, Lot_Table where Inventory_Lot.Lot_ID = Lot_Table.Lot_ID and Inventory_Lot.Lot_ID = @Lot_ID)
print @Date_In 
select @Date_Out = Inventory_Lot.IL_Date_Out from Inventory_Lot, Lot_Table where Inventory_Lot.Lot_ID = Lot_Table.Lot_ID and Inventory_Lot.Lot_ID = @Lot_ID
select @Qty_In = Inventory_Lot.IL_Qty_In from Inventory_Lot, Lot_Table where Inventory_Lot.Lot_ID = Lot_Table.Lot_ID and Inventory_Lot.Lot_ID = @Lot_ID
select @Qty_Out = Inventory_Lot.IL_Qty_Out from Inventory_Lot, Lot_Table where Inventory_Lot.Lot_ID = Lot_Table.Lot_ID and Inventory_Lot.Lot_ID = @Lot_ID
select @Lot_Number = Lot_Number from Lot_Table, Inventory_Lot, process_step where Lot_Table.Lot_ID = @Lot_ID and Lot_Table.Lot_ID = Inventory_Lot.Lot_ID and process_step.ProcessStep_ID = Lot_Table.ProcessStep_ID and (process_step.PS_Type_Name = 'Finished Goods' or process_step.PS_Type_Name = 'Die Bank' or process_step.PS_Type_Name = 'Class Store')
select @Lot_Current_Status = Lot_Current_Status from Lot_Table where Lot_ID = @Lot_ID
select @Part_Number = Part_Number from product where ProcessStep_ID = @Process_StepID
select @Lot_Cost = Lot_Cost from Lot_Table, inventory_lot where lot_table.Lot_ID = @Lot_ID and lot_table.lot_id = inventory_lot.lot_id
select @PS_Type_Name = PS_Type_Name from Process_step where ProcessStep_ID = @Process_StepID
print @Date_In
print @Date_Out

insert into @results values(@Lot_Number, @Lot_Cost, @part_Number, @Lot_Current_Status, convert(varchar, @Date_In,101), convert(varchar, @Date_Out,101), @Qty_In, @Qty_Out, @PS_Type_Name)
set @Date_In = null
set @Date_Out = null
set @Qty_In = null
set @Qty_Out = null
set @Lot_ID = null
set @Lot_Number = null
set @Lot_Current_Status = null
set @Part_Number = null
set @Lot_Cost = null
set @PS_Type_Name = null
fetch next from Lot_Cursor into @Lot_ID
end
close Lot_Cursor
deallocate Lot_Cursor

set @Process_StepId = null
fetch next from Process_FlowCursor into @Process_StepId
end
close Process_FlowCursor 
deallocate Process_FlowCursor 
end


--if (@date_from = '0' and @date_To = '0')
begin
select * from @results 
end

SET QUOTED_IDENTIFIER Off 




